﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using TarreOA.TarreOAModels;

namespace TarreOA.TarreOADAL {
    public class Flow_NewFlow_DAO {
        /// <summary>
        /// 查找指定的流程信息
        /// </summary>
        /// <param name="sql">查询语句</param>
        /// <param name="paras">参数键值对</param>
        /// <returns></returns>
        public List<Flow_NewFlow> FindNewFlows(string sql, Hashtable paras)
        {
            List<Flow_NewFlow> list = null;
            SqlConnection sqlConn = null;
            SqlCommand sqlCmd = null;
            SqlDataReader sqlDr = null;
            try
            {
                sqlConn = SQLManager.GetConn();
                sqlConn.Open();
                sqlCmd = new SqlCommand(sql, sqlConn);
                foreach (object key in paras.Keys)
                {
                    sqlCmd.Parameters.Add(new SqlParameter(key.ToString(), paras[key]));
                }
                sqlDr = sqlCmd.ExecuteReader();
                if (sqlDr.HasRows)
                {
                    list = new List<Flow_NewFlow>();
                    while (sqlDr.Read())
                    {
                        Flow_NewFlow newFlow = new Flow_NewFlow();
                        newFlow.NewFlowl_id = sqlDr["Id"] == null ? -1 : (int)sqlDr["Id"];
                        newFlow.NewFlowl_id = sqlDr["Id"] == null ? 0 : (int)sqlDr["Id"];
                        newFlow.NewFlow_name = sqlDr["New_Flow_Name"] == null ? null : (string)sqlDr["New_Flow_Name"];
                        newFlow.NewFlow_sort = sqlDr["New_Flow_Sort"] == null ? -1 : (int)sqlDr["New_Flow_Sort"];
                        newFlow.NewFlow_type = sqlDr["New_Flow_Type"] == null ? -1 : (int)sqlDr["New_Flow_Type"];
                        newFlow.NewFlow_sort = sqlDr["New_Flow_Sort"] == null ? 0 : (int)sqlDr["New_Flow_Sort"];
                        newFlow.NewFlow_type = sqlDr["New_Flow_Type"] == null ? 0 : (int)sqlDr["New_Flow_Type"];
                        newFlow.NewFlow_manager = sqlDr["New_Flow_Manager"] == null ? null : (string)sqlDr["New_Flow_Manager"];
                        newFlow.Base_flow_id = sqlDr["Base_Flow_Id"] == null ? -1 : (int)sqlDr["Base_Flow_Id"];
                        newFlow.Allow_affixFile = sqlDr["Allow_AffixFile"] == null ? -1 : (int)sqlDr["Allow_AffixFile"];
                        list.Add(newFlow);
                        newFlow.Allow_affixFile = sqlDr["Allow_AffixFile"] == null ? 0 : (int)sqlDr["Allow_AffixFile"];
                    }
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
            finally
            {
                if (sqlDr != null)
                {
                    sqlDr.Close();
                    sqlDr.Dispose();
                }
                if (sqlConn != null && sqlConn.State == ConnectionState.Open)
                {
                    sqlConn.Close();
                    sqlConn.Dispose();
                }
            }
            return list;
        }
        /// <summary>
        /// 根据分类查询进程
        /// </summary>
        /// <param name="tid"></param>
        /// <returns></returns>
        public List<Flow_NewFlow> FindFlowsByType(int tid)
        {
            List<Flow_NewFlow> list = new List<Flow_NewFlow>();
            SqlConnection conn = null;
            SqlDataReader dr = null;
            try
            {
                conn = SQLManager.GetConn();
                SqlCommand sc = new SqlCommand();
                sc.Connection = conn;
                sc.CommandText = "select * from Flow_NewFlow where Base_Flow_Id="+tid.ToString()+" order by New_Flow_Sort";
                conn.Open();
                dr = sc.ExecuteReader();
                while (dr.Read())
                {
                    Flow_NewFlow f = new Flow_NewFlow();
                    f.NewFlowl_id = (int)dr["Id"];
                    f.NewFlow_sort = (int)dr["New_Flow_Sort"];
                    f.NewFlow_name = (string)dr["New_Flow_Name"];
                    f.Allow_affixFile = (int)dr["Allow_AffixFile"];
                    f.NewFlow_type = (int)dr["New_Flow_Type"];
                    f.NewFlow_manager = (string)dr["New_Flow_Manager"];
                    Flow_Forms_DAO flow_Forms_DAO = new Flow_Forms_DAO();
                    f.Flow_Form = flow_Forms_DAO.FindByFormId(Convert.ToInt32(dr["Form_id"]));
                    Flow_Type_DAO flow_Type_DAO = new Flow_Type_DAO();
                    f.Base_flow = flow_Type_DAO.FindByTypeId(Convert.ToInt32(dr["Base_Flow_Id"]));
                    list.Add(f);
                }
            }
            catch (Exception e)
            {
                Console.WriteLine(e.Message);
            }
            finally
            {
                if (dr != null)
                {
                    if (!dr.IsClosed)
                    {
                        dr.Close();
                        dr.Dispose();
                    }
                }
                if (conn != null)
                {
                    if (conn.State == ConnectionState.Open)
                    {
                        conn.Close();
                        conn.Dispose();
                    }
                }
            }
            return list;
        }
        /// <summary>
        /// 根据流程ID查询流程信息
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public Flow_NewFlow FindByFlowId(int id)
        {
            Flow_NewFlow f = new Flow_NewFlow();
            SqlConnection conn = null;
            SqlDataReader dr = null;
            try
            {
                conn = SQLManager.GetConn();
                SqlCommand sc = new SqlCommand();
                sc.Connection = conn;
                sc.CommandText = "select * from Flow_NewFlow where Id=" + id.ToString();
                conn.Open();
                dr = sc.ExecuteReader();
                if (dr.Read())
                {
                    f.NewFlowl_id = (int)dr["Id"];
                    f.NewFlow_sort = (int)dr["New_Flow_Sort"];
                    f.NewFlow_name = (string)dr["New_Flow_Name"];
                    f.Allow_affixFile = (int)dr["Allow_AffixFile"];
                    f.NewFlow_type = (int)dr["New_Flow_Type"];
                    f.NewFlow_manager = (string)dr["New_Flow_Manager"];
                    Flow_Forms_DAO flow_Forms_DAO = new Flow_Forms_DAO();
                    f.Flow_Form = flow_Forms_DAO.FindByFormId(Convert.ToInt32(dr["Form_id"]));
                    Flow_Type_DAO flow_Type_DAO = new Flow_Type_DAO();
                    f.Base_flow = flow_Type_DAO.FindByTypeId(Convert.ToInt32(dr["Base_Flow_Id"]));
                }
            }
            catch (Exception e)
            {
                Console.WriteLine(e.Message);
            }
            finally
            {
                if (dr != null)
                {
                    if (!dr.IsClosed)
                    {
                        dr.Close();
                        dr.Dispose();
                    }
                }
                if (conn != null)
                {
                    if (conn.State == ConnectionState.Open)
                    {
                        conn.Close();
                        conn.Dispose();
                    }
                }
            }
            return f;
        }
        /// <summary>
        /// 添加流程
        /// </summary>
        /// <param name="nf"></param>
        /// <returns></returns>
        public bool AddNewFlows(Flow_NewFlow nf)
        {
            bool flag = false;
            SqlConnection conn = null;
            try
            {
                conn = SQLManager.GetConn();
                SqlCommand sc = new SqlCommand();
                sc.Connection = conn;
                conn.Open();
                sc.CommandText = "insert Flow_NewFlow(Base_Flow_Id,New_Flow_Sort,New_Flow_Name,New_Flow_Type,Form_id,Allow_AffixFile,New_Flow_Manager) values(@Base_Flow_Id,@New_Flow_Sort,@New_Flow_Name,@New_Flow_Type,@Form_id,@Allow_AffixFile,'')";
                sc.Parameters.Add(new SqlParameter("@Base_Flow_Id", nf.Base_flow.Id));
                sc.Parameters.Add(new SqlParameter("@New_Flow_Sort", nf.NewFlow_sort));
                sc.Parameters.Add(new SqlParameter("@New_Flow_Name", nf.NewFlow_name));
                sc.Parameters.Add(new SqlParameter("@New_Flow_Type", nf.NewFlow_type));
                sc.Parameters.Add(new SqlParameter("@Form_id",nf.Flow_Form.Id ));
                sc.Parameters.Add(new SqlParameter("@Allow_AffixFile", nf.Allow_affixFile));
                int i = sc.ExecuteNonQuery();
                if (i > 0)
                {
                    flag = true;
                }
            }
            catch (Exception e)
            {
                Console.WriteLine(e.Message);
            }
            finally
            {
                if (conn != null)
                {
                    if (conn.State == ConnectionState.Open)
                    {
                        conn.Close();
                        conn.Dispose();
                    }
                }
            }
            return flag;
        }
        
        /// <summary>
        /// 修改流程属性
        /// </summary>
        /// <param name="nf"></param>
        /// <returns></returns>
        public bool UpdateNewFlow(Flow_NewFlow nf)
        {
            bool flag = false;
            SqlConnection conn = null;
            try
            {
                conn = SQLManager.GetConn();
                SqlCommand sc = new SqlCommand();
                sc.Connection = conn;
                conn.Open();
                sc.CommandText = "update Flow_NewFlow set Base_Flow_Id=@Base_Flow_Id,New_Flow_Sort=@New_Flow_Sort,New_Flow_Name=@New_Flow_Name,New_Flow_Type=@New_Flow_Type,Form_id=@Form_id,Allow_AffixFile=@Allow_AffixFile where Id=@Id";
                sc.Parameters.Add(new SqlParameter("@Base_Flow_Id", nf.Base_flow.Id));
                sc.Parameters.Add(new SqlParameter("@New_Flow_Sort", nf.NewFlow_sort));
                sc.Parameters.Add(new SqlParameter("@New_Flow_Name", nf.NewFlow_name));
                sc.Parameters.Add(new SqlParameter("@New_Flow_Type", nf.NewFlow_type));
                sc.Parameters.Add(new SqlParameter("@Form_id", nf.Flow_Form.Id));
                sc.Parameters.Add(new SqlParameter("@Allow_AffixFile", nf.Allow_affixFile));
                sc.Parameters.Add(new SqlParameter("@Id", nf.NewFlowl_id));
                int i = sc.ExecuteNonQuery();
                if (i > 0)
                {
                    flag = true;
                }
            }
            catch (Exception e)
            {
                Console.WriteLine(e.Message);
            }
            finally
            {
                if (conn != null)
                {
                    if (conn.State == ConnectionState.Open)
                    {
                        conn.Close();
                        conn.Dispose();
                    }
                }
            }
            return flag;
        }
        /// <summary>
        /// 修改监控人员
        /// </summary>
        /// <param name="nf"></param>
        /// <returns></returns>
        public bool UpdateFlowManager(Flow_NewFlow nf)
        {
            bool flag = false;
            SqlConnection conn = null;
            try
            {
                conn = SQLManager.GetConn();
                SqlCommand sc = new SqlCommand();
                sc.Connection = conn;
                conn.Open();
                sc.CommandText = "update Flow_NewFlow set New_Flow_Manager=@New_Flow_Manager where Id=@Id";
                sc.Parameters.Add(new SqlParameter("@New_Flow_Manager", nf.NewFlow_manager));
                sc.Parameters.Add(new SqlParameter("@Id", nf.NewFlowl_id));
                int i = sc.ExecuteNonQuery();
                if (i > 0)
                {
                    flag = true;
                }
            }
            catch (Exception e)
            {
                Console.WriteLine(e.Message);
            }
            finally
            {
                if (conn != null)
                {
                    if (conn.State == ConnectionState.Open)
                    {
                        conn.Close();
                        conn.Dispose();
                    }
                }
            }
            return flag;
        }
        /// <summary>
        /// 删除流程信息
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public bool DelNewFlow(int id)
        {
            bool flag = false;
            SqlConnection conn = null;
            try
            {
                conn = SQLManager.GetConn();
                SqlCommand sc = new SqlCommand();
                sc.Connection = conn;
                conn.Open();
                sc.CommandText = "delete from Flow_NewFlow where Id="+id.ToString();
                int i = sc.ExecuteNonQuery();
                if (i > 0)
                {
                    flag = true;
                }
            }
            catch (Exception e)
            {
                Console.WriteLine(e.Message);
            }
            finally
            {
                if (conn != null)
                {
                    if (conn.State == ConnectionState.Open)
                    {
                        conn.Close();
                        conn.Dispose();
                    }
                }
            }
            return flag;
        }
        /// <summary>
        /// 流程客隆
        /// </summary>
        /// <param name="fnf"></param>
        /// <returns></returns>
        public bool CopyNewFlow(Flow_NewFlow fnf)
        {
            bool flag = false;
            SqlConnection conn = null;
            SqlTransaction st = null;
            SqlDataReader dr = null;
            SqlDataReader dr1 = null;
            try
            {
                conn = SQLManager.GetConn();
                SqlCommand sc = new SqlCommand();
                sc.Connection = conn;
                conn.Open();
                st = conn.BeginTransaction();
                sc.Transaction = st;
                //查询要客隆流程信息
                sc.CommandText = "select * from Flow_NewFlow where Id="+fnf.NewFlowl_id.ToString();
                dr = sc.ExecuteReader();
                Flow_NewFlow nf = null;
                if (dr.Read())
                {
                    nf = new Flow_NewFlow();
                    nf.NewFlowl_id = (int)dr["Id"];
                    nf.NewFlow_sort = fnf.NewFlow_sort;
                    nf.NewFlow_name = fnf.NewFlow_name;
                    nf.Allow_affixFile = (int)dr["Allow_AffixFile"];
                    nf.NewFlow_type = (int)dr["New_Flow_Type"];
                    nf.NewFlow_manager = (string)dr["New_Flow_Manager"];
                    Flow_Forms ff = new Flow_Forms();
                    ff.Id = Convert.ToInt32(dr["Form_id"]);
                    nf.Flow_Form = ff;
                    Flow_Type ft = new Flow_Type();
                    ft.Id = Convert.ToInt32(dr["Base_Flow_Id"]);
                    nf.Base_flow = ft;
                }
                if (dr!=null)
                {
                    dr.Close();
                    dr.Dispose();
                }
                //客隆流程信息
                sc.CommandText = "insert Flow_NewFlow(Base_Flow_Id,New_Flow_Sort,New_Flow_Name,New_Flow_Type,Form_id,Allow_AffixFile,New_Flow_Manager) values(@Base_Flow_Id,@New_Flow_Sort,@New_Flow_Name,@New_Flow_Type,@Form_id,@Allow_AffixFile,@New_Flow_Manager)";
                sc.Parameters.Add(new SqlParameter("@Base_Flow_Id", nf.Base_flow.Id));
                sc.Parameters.Add(new SqlParameter("@New_Flow_Sort", nf.NewFlow_sort));
                sc.Parameters.Add(new SqlParameter("@New_Flow_Name", nf.NewFlow_name));
                sc.Parameters.Add(new SqlParameter("@New_Flow_Type", nf.NewFlow_type));
                sc.Parameters.Add(new SqlParameter("@Form_id", nf.Flow_Form.Id));
                sc.Parameters.Add(new SqlParameter("@Allow_AffixFile", nf.Allow_affixFile));
                sc.Parameters.Add(new SqlParameter("@New_Flow_Manager", nf.NewFlow_manager));
                sc.ExecuteNonQuery();
                //查询新插入的流程信息
                sc.CommandText = "select Id from Flow_NewFlow where New_Flow_Sort=" + nf.NewFlow_sort.ToString() + " and New_Flow_Name='" + nf.NewFlow_name + "' and Base_Flow_Id="+nf.Base_flow.Id.ToString()+" order by Id desc";
                Object o = sc.ExecuteScalar();
                int newflowid = 0;
                if (o!= null)
                {
                    newflowid = (int)o;
                }
                //获取该流程的流程步骤信息
                sc.CommandText = "select * from Flow_NewFlowStep where New_Flow_Id=" + fnf.NewFlowl_id.ToString();
                List<Flow_NewFlowStep> flowStepList = new List<Flow_NewFlowStep>();
                dr1 = sc.ExecuteReader();
                while (dr1.Read())
                {
                    Flow_NewFlowStep flowStep = new Flow_NewFlowStep();
                    flowStep.NewFlowStep_id = (int)dr1["Id"];
                    flowStep.NextPrc_id = (string)dr1["Next_Prcs_Id"];
                    flowStep.Prc_inputs = (string)dr1["Prc_Inputs"];
                    flowStep.Prc_name = (string)dr1["Prc_Name"];
                    flowStep.Prc_roles = (string)dr1["Prc_Roles"];
                    flowStep.Prc_sections = (string)dr1["Prc_Sections"];
                    flowStep.Prc_users = (string)dr1["Prc_Users"];
                    flowStep.Sort_id = (int)dr1["Sort_Id"];
                    Flow_NewFlow flow_NewFlow = new Flow_NewFlow();
                    flow_NewFlow.NewFlowl_id = newflowid;
                    flowStep.Base_newFlow = flow_NewFlow;
                    flowStepList.Add(flowStep);
                }
                if (dr1 != null)
                {
                    dr1.Close();
                    dr1.Dispose();
                }
                //客隆流程步骤
                foreach (Flow_NewFlowStep var in flowStepList)
                {
                    sc.CommandText = "insert Flow_NewFlowStep(New_Flow_Id,Sort_Id,Prc_Name,Prc_Inputs,Prc_Users,Prc_Sections,Prc_Roles,Next_Prcs_Id) values(" + var.Base_newFlow.NewFlowl_id + "," + var.Sort_id + ",'" + var.Prc_name + "','" + var.Prc_inputs + "','" + var.Prc_users + "','" + var.Prc_sections + "','" + var.Prc_roles + "','"+var.NextPrc_id+"')";
                    sc.ExecuteNonQuery();
                }
                st.Commit();
                flag = true;
            }
            catch (Exception e)
            {
                try
                {
                    st.Rollback();
                }
                catch (Exception ex)
                {
                    Console.WriteLine(ex.Message);
                }
                Console.WriteLine(e.Message);
            }
            finally
            {
                if (conn.State == ConnectionState.Open)
                {
                    conn.Close();
                    conn.Dispose();
                }
            }
            return flag;
        }
    }
}
